package com.hsnn.medstgmini.pro.project.dao.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.model.form.StdProdDrugInfoForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.ProdDrugProfileDao;
import com.hsnn.medstgmini.pro.project.model.form.ProdDrugProfileForm;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.MapToBeanUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 生产企业药品资质申报管理
 * @author 蔡春龙
 * @date 2015年8月28日
 */
@Repository
public class ProdDrugProfileDaoImpl implements ProdDrugProfileDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;

	/**
	 * @category 查询企业药品资质列表
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	public Pagination getProdDrugProfileList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT c.proj_proddrug_id projProddrugId, a.compprofile_id compprofileId, a.comp_id compId, a.comp_prod_id compProdId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks FROM std_compdrugprofile a JOIN bid_projproddrug c ON a.comp_prod_id = c.prod_id AND c.proj_id = :projId WHERE 1 = 1 AND a.comp_id = :compId AND a.comp_prod_id = :prodId AND a.status = 1 AND NOT EXISTS ( SELECT b.compdrugprofile_id FROM bid_compdrugprofile b WHERE b.proj_id = c.proj_id AND b.proj_proddrug_id = c.proj_proddrug_id AND b.compdrugprofile_id = a.compprofile_id ) ");

		ProdDrugProfileForm form = (ProdDrugProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());
		params.put("prodId", form.getProdId());

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 申报产品资质
	 * @date 2015年8月31日
	 * @param list
	 * @param user
	 */
	public void submitProdDrugProfileList(List<ProdDrugProfileForm> list,
			SysUser user) {
		Session session = factory.getCurrentSession();
		if (list != null && !list.isEmpty()) {
			List<String> ids = new LinkedList<String>();
			for (ProdDrugProfileForm form : list) {
				String sql = " SELECT COUNT(*) FROM bid_compdrugprofile b WHERE b.proj_id = :projId AND b.proj_proddrug_id = :projProddrugId AND b.compdrugprofile_id = :drugProfileId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("projId", form.getProjId());
				query.setString("projProddrugId", form.getProjProddrugId());
				query.setString("drugProfileId", form.getDrugProfileId());
				Long records = ((BigInteger) query.uniqueResult()).longValue();

				if (records == 0) {
					String id = UUID.randomUUID().toString();
					ids.add(id);
					sql = " INSERT INTO bid_compdrugprofile(bid_compdrugprofile_id, proj_id, proj_proddrug_id, compdrugprofile_id,apply_type, audit_status, complain_status, upd_datetime, upd_user) VALUES (:id, :projId, :projProddrugId, :drugProfileId,1,0,3, sysdate(), :updUser) ";
					query = session.createSQLQuery(sql);
					query.setString("id", id);
					query.setString("projId", form.getProjId());
					query.setString("projProddrugId", form.getProjProddrugId());
					query.setString("drugProfileId", form.getDrugProfileId());
					query.setString("updUser", user.getId());
					query.executeUpdate();
				}
			}
			session.flush();

			saveDrugProfileLog(ids);
		}
	}

	/**
	 * @category 保存产品资质申报日志
	 * @date 2015年8月31日
	 * @param ids
	 */
	private void saveDrugProfileLog(List<String> ids) {
		if (ids != null && !ids.isEmpty()) {
			Session session = factory.getCurrentSession();
			String sql = " INSERT INTO bid_compdrugprofile_log SELECT UUID(), a.* FROM bid_compdrugprofile a WHERE bid_compdrugprofile_id IN (:ids) ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameterList("ids", ids);
			query.executeUpdate();
			session.flush();
		}
	}

	/**
	 * @category 企业药品资质审核结果列表
	 * @date 2015年8月31日
	 * @param page
	 * @return
	 */
	public Pagination getDrugProfileAuditResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.comp_prod_id compProdId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT(a.profile_starttime, '%Y-%m-%d')  profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks, b.bid_compdrugprofile_id bidCompDrugProfileId, a.audit_status auditStatus, a.audit_datetime auditDatetime, a.audit_remark auditRemark FROM std_compdrugprofile a JOIN bid_compdrugprofile b ON b.compdrugprofile_id = a.compprofile_id WHERE 1 = 1 AND a.comp_id = :compId AND a.comp_prod_id = :prodId AND a.status = 1 AND b.proj_id = :projId ");

		ProdDrugProfileForm form = (ProdDrugProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());
		params.put("prodId", form.getProdId());

		Integer auditStatus = form.getAuditStatus();
		if (auditStatus != null) {
			sb.append(" AND b.audit_status = :auditStatus ");
			params.put("auditStatus", auditStatus);
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}
	
	/**
	 * @category 查询药品资质澄清列表
	 * @date 2015年8月31日
	 * @param page
	 * @return
	 */
	public Pagination getDrugProfileComplainList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.comp_prod_id compProdId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks, b.bid_compdrugprofile_id bidCompDrugProfileId, b.audit_status auditStatus, b.audit_datetime auditDatetime, b.audit_remark auditRemark FROM std_compdrugprofile a JOIN bid_compdrugprofile b ON b.compdrugprofile_id = a.compprofile_id WHERE 1 = 1 AND a.comp_id = :compId AND a.comp_prod_id = :prodId AND a.status = 1 AND b.proj_id = :projId AND b.audit_status = 2 AND b.complain_status = 3 ");

		ProdDrugProfileForm form = (ProdDrugProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());
		params.put("prodId", form.getProdId());

		Integer auditStatus = form.getAuditStatus();
		if (auditStatus != null) {
			sb.append(" AND b.audit_status = :auditStatus ");
			params.put("auditStatus", auditStatus);
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}
	
	/**
	 * @category 查询药品资质澄清结果列表
	 * @date 2015年8月31日
	 * @param page
	 * @return
	 */
	public Pagination getDrugProfileComplainResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.comp_prod_id compProdId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks, b.bid_compdrugprofile_id bidCompDrugProfileId, b.audit_status auditStatus, b.audit_datetime auditDatetime, b.audit_remark auditRemark, b.complain_status complainStatus, b.complain_audit_remark complainAuditRemark, b.complain_audit_datetime complainAuditDatetime FROM std_compdrugprofile a JOIN bid_compdrugprofile b ON b.compdrugprofile_id = a.compprofile_id WHERE 1 = 1 AND a.comp_id = :compId AND a.comp_prod_id = :prodId AND a.status = 1 AND b.proj_id = :projId AND b.audit_status = 2 AND b.complain_status <> 3 ");

		ProdDrugProfileForm form = (ProdDrugProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());
		params.put("prodId", form.getProdId());

		Integer auditStatus = form.getAuditStatus();
		if (auditStatus != null) {
			sb.append(" AND b.audit_status = :auditStatus ");
			params.put("auditStatus", auditStatus);
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}

	

	/**
	 * @category 澄清药品资质
	 * @date 2015年9月1日
	 * @param stdCompdrugprofile
	 * @param form
	 */
	public void complainDrugProfile(ProdDrugProfileForm form, SysUser user) {
		if (form != null) {
			String bidCompDrugProfileId = form.getBidCompDrugProfileId();
			if (StringUtils.isNotBlank(bidCompDrugProfileId)) {
				Session session = factory.getCurrentSession();
				String sql = " update bid_compdrugprofile set complain_status = 0, complain_datetime = sysdate(), complain_remark = :complainRemark, upd_datetime = sysdate(), upd_user = :updUser where bid_compdrugprofile_id = :bidCompDrugProfileId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("complainRemark", form.getComplainRemark());
				query.setString("bidCompDrugProfileId", bidCompDrugProfileId);
				query.setString("updUser", user.getId());
				query.executeUpdate();
				session.flush();

				// 记录日志
				List<String> ids = new LinkedList<String>();
				ids.add(bidCompDrugProfileId);
				saveDrugProfileLog(ids);
			}
		}
	}

	/**
	 * @category 药品资质申报页面
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getProdDrugListDeclare(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT e.proj_id projId, a.prod_id prodId, MAX(a.qualityspec_id) qualityspecId, MAX(a.drug_code) drugCode, MAX(b.qualityspec_name) qualityspecName, MAX(b.form) form, MAX(b.spec) spec, MAX(a.factor) factor, MAX(a.unit) unit, MAX(d.material_id) materialId, MAX(d.material_name) materialName, MAX(a.prod_comp_id) prodCompId, MAX(c.comp_name) prodCompName, MAX(a.status) status, MAX(f.ref_price) refPrice, MAX(f.dir_price) dirPrice, MAX(f.is_bus_scope) isBusScope, MAX(f.is_semi_bidding) isSemiBidding, MAX(f.is_bidding) isBidding, MAX(f.audit_status) auditStatus, MAX(f.audit_remark) auditRemark, DATE_FORMAT(MAX(f.audit_datetime), '%Y-%m-%d %T') auditDatetime, MAX(f.proj_proddrug_id) projProdDrugId, MAX(g.proj_code) as projCode, MAX(g.name) as projName, COUNT(j.compprofile_id) totalCount, COUNT(k.compdrugprofile_id) declaredCount, DATE_FORMAT(MAX(h.pro_submit_starttime), '%Y-%m-%d %T') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime), '%Y-%m-%d %T') proSubmitEndtime, DATE_FORMAT(MAX(h.pro_audit_pub_starttime), '%Y-%m-%d %T') proAuditPubStarttime, DATE_FORMAT(MAX(h.pro_audit_pub_endtime), '%Y-%m-%d %T') proAuditPubEndtime, DATE_FORMAT(MAX(h.complain_starttime), '%Y-%m-%d %T') complainStarttime, DATE_FORMAT(MAX(h.complain_endtime), '%Y-%m-%d %T') complainEndtime, DATE_FORMAT(MAX(h.complain_pub_starttime), '%Y-%m-%d %T') complainPubStarttime, DATE_FORMAT(MAX(h.complain_pub_endtime), '%Y-%m-%d %T') complainPubEndtime FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id join view_bidproject g ON e.proj_id = g.proj_id left join bid_project_details h ON g.proj_id = h.proj_id JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND e.proj_id = f.proj_id left JOIN std_compdrugprofile j ON j.comp_id = a.prod_comp_id AND a.prod_id = j.comp_prod_id LEFT JOIN bid_compdrugprofile k ON e.proj_id = k.proj_id AND f.proj_proddrug_id = k.proj_proddrug_id AND j.compprofile_id = k.compdrugprofile_id WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId AND g.starttime <= SYSDATE() AND g.endtime >= SYSDATE() ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			// 项目ID
			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND e.proj_id =:projId ");
				params.put("projId",projId);
			}
		}

		sb.append("  GROUP BY e.proj_id, a.prod_id  ORDER BY e.proj_id ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}
	
	/**
	 * @category 药品资审查看页面
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getProdDrugListDeclareSee(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT e.proj_id projId, a.prod_id prodId, MAX(a.qualityspec_id) qualityspecId, MAX(a.drug_code) drugCode, MAX(b.qualityspec_name) qualityspecName, MAX(b.form) form, MAX(b.spec) spec, MAX(a.factor) factor, MAX(a.unit) unit, MAX(d.material_id) materialId, MAX(d.material_name) materialName, MAX(a.prod_comp_id) prodCompId, MAX(c.comp_name) prodCompName, MAX(a.status) status, MAX(f.ref_price) refPrice, MAX(f.dir_price) dirPrice, MAX(f.is_bus_scope) isBusScope, MAX(f.is_semi_bidding) isSemiBidding, MAX(f.is_bidding) isBidding, MAX(f.audit_status) auditStatus, MAX(f.audit_remark) auditRemark, DATE_FORMAT(MAX(f.audit_datetime), '%Y-%m-%d %T') auditDatetime, MAX(f.proj_proddrug_id) projProdDrugId, MAX(g.proj_code) as projCode, MAX(g.name) as projName, COUNT(j.compprofile_id) totalCount, SUM(CASE WHEN k.audit_status = 1 THEN 1 ELSE 0 END) approveCount, SUM(CASE WHEN k.audit_status = 2 THEN 1 ELSE 0 END) unapproveCount, DATE_FORMAT(MAX(h.pro_submit_starttime), '%Y-%m-%d %T') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime), '%Y-%m-%d %T') proSubmitEndtime, DATE_FORMAT(MAX(h.pro_audit_pub_starttime), '%Y-%m-%d %T') proAuditPubStarttime, DATE_FORMAT(MAX(h.pro_audit_pub_endtime), '%Y-%m-%d %T') proAuditPubEndtime, DATE_FORMAT(MAX(h.complain_starttime), '%Y-%m-%d %T') complainStarttime, DATE_FORMAT(MAX(h.complain_endtime), '%Y-%m-%d %T') complainEndtime, DATE_FORMAT(MAX(h.complain_pub_starttime), '%Y-%m-%d %T') complainPubStarttime, DATE_FORMAT(MAX(h.complain_pub_endtime), '%Y-%m-%d %T') complainPubEndtime FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id join view_bidproject g ON e.proj_id = g.proj_id left join bid_project_details h ON g.proj_id = h.proj_id JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND e.proj_id = f.proj_id JOIN std_compdrugprofile j ON j.comp_id = a.prod_comp_id AND a.prod_id = j.comp_prod_id JOIN bid_compdrugprofile k ON e.proj_id = k.proj_id AND f.proj_proddrug_id = k.proj_proddrug_id AND j.compprofile_id = k.compdrugprofile_id WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId AND g.starttime <= SYSDATE() AND g.endtime >= SYSDATE() ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND e.proj_id = :projId ");
				params.put("projId",projId);
			}
		}

		sb.append(" GROUP BY e.proj_id, a.prod_id ORDER BY e.proj_id ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}
	
	/**
	 * @category 药品资审澄清页面查询
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getProdDrugListClarify(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT g.starttime, g.endtime, e.proj_id projId, a.prod_id prodId, MAX(a.qualityspec_id) qualityspecId, MAX(a.drug_code) drugCode, MAX(b.qualityspec_name) qualityspecName, MAX(b.form) form, MAX(b.spec) spec, MAX(a.factor) factor, MAX(a.unit) unit, MAX(d.material_id) materialId, MAX(d.material_name) materialName, MAX(a.prod_comp_id) prodCompId, MAX(c.comp_name) prodCompName, MAX(a.status) status, MAX(f.ref_price) refPrice, MAX(f.dir_price) dirPrice, MAX(f.is_bus_scope) isBusScope, MAX(f.is_semi_bidding) isSemiBidding, MAX(f.is_bidding) isBidding, MAX(f.audit_status) auditStatus, MAX(f.audit_remark) auditRemark, DATE_FORMAT(MAX(f.audit_datetime), '%Y-%m-%d %T') auditDatetime, MAX(f.proj_proddrug_id) projProdDrugId, MAX(g.proj_code) as projCode, MAX(g.name) as projName, COUNT(j.compprofile_id) totalCount, SUM(CASE WHEN k.complain_status = 3 THEN 1 ELSE 0 END) unsettleCount, DATE_FORMAT(MAX(h.pro_submit_starttime), '%Y-%m-%d %T') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime), '%Y-%m-%d %T') proSubmitEndtime, DATE_FORMAT(MAX(h.pro_audit_pub_starttime), '%Y-%m-%d %T') proAuditPubStarttime, DATE_FORMAT(MAX(h.pro_audit_pub_endtime), '%Y-%m-%d %T') proAuditPubEndtime, DATE_FORMAT(MAX(h.complain_starttime), '%Y-%m-%d %T') complainStarttime, DATE_FORMAT(MAX(h.complain_endtime), '%Y-%m-%d %T') complainEndtime, DATE_FORMAT(MAX(h.complain_pub_starttime), '%Y-%m-%d %T') complainPubStarttime, DATE_FORMAT(MAX(h.complain_pub_endtime), '%Y-%m-%d %T') complainPubEndtime FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id join view_bidproject g ON e.proj_id = g.proj_id left join bid_project_details h ON g.proj_id = h.proj_id JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND e.proj_id = f.proj_id JOIN std_compdrugprofile j ON j.comp_id = a.prod_comp_id AND a.prod_id = j.comp_prod_id JOIN bid_compdrugprofile k ON e.proj_id = k.proj_id AND f.proj_proddrug_id = k.proj_proddrug_id AND j.compprofile_id = k.compdrugprofile_id WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId AND g.starttime <= SYSDATE() AND g.endtime >= SYSDATE() AND k.audit_status = 2 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND e.proj_id = :projId ");
				params.put("projId",projId);
			}
		}

		sb.append(" GROUP BY e.proj_id, a.prod_id  ORDER BY e.proj_id ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}
	
	/**
	 * @category 药品资审澄清查看页面查询
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getProdDrugListClarifySee(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT g.starttime, g.endtime, e.proj_id projId, a.prod_id prodId, MAX(a.qualityspec_id) qualityspecId, MAX(a.drug_code) drugCode, MAX(b.qualityspec_name) qualityspecName, MAX(b.form) form, MAX(b.spec) spec, MAX(a.factor) factor, MAX(a.unit) unit, MAX(d.material_id) materialId, MAX(d.material_name) materialName, MAX(a.prod_comp_id) prodCompId, MAX(c.comp_name) prodCompName, MAX(a.status) status, MAX(f.ref_price) refPrice, MAX(f.dir_price) dirPrice, MAX(f.is_bus_scope) isBusScope, MAX(f.is_semi_bidding) isSemiBidding, MAX(f.is_bidding) isBidding, MAX(f.audit_status) auditStatus, MAX(f.audit_remark) auditRemark, DATE_FORMAT(MAX(f.audit_datetime), '%Y-%m-%d %T') auditDatetime, MAX(f.proj_proddrug_id) projProdDrugId, MAX(g.proj_code) as projCode, MAX(g.name) as projName, COUNT(j.compprofile_id) totalCount, SUM(CASE WHEN k.complain_status = 1 THEN 1 ELSE 0 END) settleApproveCount, SUM(CASE WHEN k.complain_status = 2 THEN 1 ELSE 0 END) settleUnsettleCount, DATE_FORMAT(MAX(h.pro_submit_starttime), '%Y-%m-%d %T') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime), '%Y-%m-%d %T') proSubmitEndtime, DATE_FORMAT(MAX(h.pro_audit_pub_starttime), '%Y-%m-%d %T') proAuditPubStarttime, DATE_FORMAT(MAX(h.pro_audit_pub_endtime), '%Y-%m-%d %T') proAuditPubEndtime, DATE_FORMAT(MAX(h.complain_starttime), '%Y-%m-%d %T') complainStarttime, DATE_FORMAT(MAX(h.complain_endtime), '%Y-%m-%d %T') complainEndtime, DATE_FORMAT(MAX(h.complain_pub_starttime), '%Y-%m-%d %T') complainPubStarttime, DATE_FORMAT(MAX(h.complain_pub_endtime), '%Y-%m-%d %T') complainPubEndtime FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id join view_bidproject g ON e.proj_id = g.proj_id left join bid_project_details h ON g.proj_id = h.proj_id JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND e.proj_id = f.proj_id JOIN std_compdrugprofile j ON j.comp_id = a.prod_comp_id AND a.prod_id = j.comp_prod_id JOIN bid_compdrugprofile k ON e.proj_id = k.proj_id AND f.proj_proddrug_id = k.proj_proddrug_id AND j.compprofile_id = k.compdrugprofile_id WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId AND g.starttime <= SYSDATE() AND g.endtime >= SYSDATE() AND k.audit_status = 2 AND (k.complain_status = 0 OR k.complain_status = 1 OR k.complain_status = 2)  ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND e.proj_id = :projId ");
				params.put("projId",projId);
			}
		}

		sb.append(" GROUP BY e.proj_id, a.prod_id ORDER BY e.proj_id ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}

}
